﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Microsoft.IdentityModel.Protocols;
using System.Configuration;

namespace DataAccess
{
    public static class DA
        {
            static SqlConnection Conn = null;
            static SqlCommand cmd = null;
            static SqlDataAdapter sda = null;
            static DataTable dt = null;

            static DA()
            {
                Conn = new SqlConnection();
                Conn.ConnectionString = ConfigurationManager.ConnectionStrings["Data Source=DESKTOP-E8B0E71\\MSSQLSERVER2;Initial Catalog=JobPositionDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"].ToString();
                cmd = new SqlCommand();
                cmd.Connection = Conn;
            }

            /// <summary>
            /// 从数据库中获取数据表
            /// </summary>
            /// <param name="query">数据库执行语句 或 存储结构名称</param>
            /// <param name="commandType">数据库执行方法类型</param>
            /// <param name="paras">SqlCommand的执行参数，对应AddWithValue方法的第一个参数</param>
            /// <param name="values">SqlCommand的执行参数，对应AddWithValue方法的第二个参数</param>
            /// <returns>DataTable表类型数据</returns>
            public static DataTable GetDataTable(string query, CommandType commandType, string[] paras, object[] values)
            {
                dt = new DataTable();
                if (Conn.State != ConnectionState.Open)
                    Conn.Open();
                cmd.CommandText = query;
                cmd.CommandType = commandType;
                if (paras != null)
                {
                    for (int i = 0; i < paras.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paras[i], values[i]);
                    }
                }
                sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                cmd.Parameters.Clear();
                Conn.Close();
                return dt;
            }

            /// <summary>
            /// 获取数据库执行增删改操作的执行结果返回值
            /// </summary>
            /// <param name="query">数据库执行语句 或 存储结构名称</param>
            /// <param name="commandType">数据库执行方法类型</param>
            /// <param name="paras">SqlCommand的执行参数，对应AddWithValue方法的第一个参数</param>
            /// <param name="values">SqlCommand的执行参数，对应AddWithValue方法的第二个参数</param>
            /// <returns>int类型受影响行数</returns>
            public static int ExcuteSql(string query, CommandType commandType, string[] paras, object[] values)
            {
                int res = 0;
                if (Conn.State != ConnectionState.Open)
                    Conn.Open();
                cmd.CommandText = query;
                cmd.CommandType = commandType;
                if (paras != null)
                {
                    for (int i = 0; i < paras.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paras[i], values[i]);
                    }
                }
                res = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                Conn.Close();
                return res;
            }

            /// <summary>
            /// 返回从数据库中查询得到的单一数据
            /// </summary>
            /// <param name="query">数据库执行语句 或 存储结构名称</param>
            /// <param name="commandType">数据库执行方法类型</param>
            /// <param name="paras">SqlCommand的执行参数，对应AddWithValue方法的第一个参数</param>
            /// <param name="values">SqlCommand的执行参数，对应AddWithValue方法的第二个参数</param>
            /// <returns>object类型返回单一数据</returns>
            public static object GetOneData(string query, CommandType commandType, string[] paras, object[] values)
            {
                object obj = null;
                if (Conn.State != ConnectionState.Open)
                    Conn.Open();
                cmd.CommandText = query;
                cmd.CommandType = commandType;
                if (paras != null)
                {
                    for (int i = 0; i < paras.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paras[i], values[i]);
                    }
                }
                obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                Conn.Close();
                return obj;
            }
        }
    }

